﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Data;

namespace ExcelToSql.Code
{
    public class DataColumnDef
    {
        private string _name, _notes1, _notes2, _DNDataType, _SqlDataType;
        private bool _import;
        private int _sqlLength;

        public string SqlDataType
        {
            get { return _SqlDataType; }
            set { _SqlDataType = value; }
        }

        public string DNDataType
        {
            get { return _DNDataType; }
            set { _DNDataType = value; }
        }

        public string Notes2
        {
            get { return _notes2; }
            set { _notes2 = value; }
        }

        public string Notes1
        {
            get { return _notes1; }
            set { _notes1 = value; }
        }

        public int SqlLength
        {
            get { return _sqlLength; }
            set { _sqlLength = value; }
        }

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        public bool Import
        {
            get { return _import; }
            set { _import = value; }
        }

        public static List<DataColumnDef> ParseFromFile(string path)
        {
            StreamReader sr = new StreamReader(path);
            List<DataColumnDef> retDefs = new List<DataColumnDef>();

            try
            {
                // Skip the first row, it contains column descriptions
                sr.ReadLine();

                while (!sr.EndOfStream)
                {
                    string[] data = Regex.Split(sr.ReadLine(), "\t");
                    DataColumnDef def = new DataColumnDef();
                    def.Notes1 = data[1];
                    def.Notes2 = data[2];

                    if (String.IsNullOrEmpty(data[0]))
                        def.Import = false;
                    else
                    {
                        def.Import = true;
                        def.Name = Regex.Replace(data[0], "[^a-zA-Z0-9]", "_");

                        // SQL Columns cannot begin with a number; append col_ to fix
                        if (Regex.Match(def.Name.Substring(0, 1), "[0-9]").Success)
                            def.Name = "col_" + def.Name;

                        def.DNDataType = data[3];
                        def.SqlDataType = data[4];

                        try
                        {
                            def.SqlLength = Int32.Parse(data[5]);
                        }
                        catch { }
                    }

                    retDefs.Add(def);
                }
            }
            finally
            {
                sr.Close();
            }

            return retDefs;
        }

        public static List<DataColumnDef> RedefineColLengths(DataSet dsData, List<DataColumnDef> defs)
        {
            List<String> stringCols = (from cd in defs
                                       where cd.SqlDataType != null
                                       && cd.SqlLength > -1
                                       && cd.SqlDataType.ToLower() == "varchar"
                                       select cd.Name).ToList<string>();

            foreach (string colName in stringCols)
            {
                /*
                int len = dsData.Tables[0].AsEnumerable()
                    .Select(row => row[colName]).OfType<string>()
                    .Max(val => val.Length);
                */

                int len = defs.Single(cd => cd.Name == colName).SqlLength;

                // Set length to something a little more friendly; < 255 becomes 255, 255 - 4000 becomes the next largest number
                // divisble by 100
                if (len < 255)
                    len = 255;
                else if (len < 4000)
                    len = Convert.ToInt32(Math.Ceiling(len / 100.0) * 100);
                else
                    len = -1;


                defs.First<DataColumnDef>(cd => cd.Name == colName).SqlLength = len;
            }

            return defs;

        }

        public static DataColumnDef ParseFromType(Type type)
        {
            DataColumnDef retDef = new DataColumnDef();
            retDef.Import = true;
            switch (type.Name)
            {
                case "Boolean":
                    retDef.DNDataType = "System.Boolean";
                    retDef.SqlDataType = "Bit";
                    return retDef;
                case "String":
                    retDef.DNDataType = "System.String";
                    retDef.SqlDataType = "VarChar";
                    return retDef;
                case "Int32":
                    retDef.DNDataType = "System.Int32";
                    retDef.SqlDataType = "int";
                    return retDef;
                case "Int64":
                    retDef.DNDataType = "System.Int64";
                    retDef.SqlDataType = "BigInt";
                    return retDef;
                case "DateTime":
                    retDef.DNDataType = "System.DateTime";
                    retDef.SqlDataType = "DateTime";
                    return retDef;
                case "Double":
                    retDef.DNDataType = "System.Double";
                    retDef.SqlDataType = "Float";
                    return retDef;

                default:
                    throw new ApplicationException("Type is not valid for this function.");

            }
        }

        public static string CreateTableStatement(List<DataColumnDef> data, string TableName)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.AppendLine(String.Format("if object_id('etl.{0}') is not null drop table etl.{0};", TableName));
            sbSQL.AppendLine(String.Format("create table ETL.{0} ( ", TableName));

            foreach (DataColumnDef def in data.Where(d => d.Import))
            {
                string colWidth = "";
                if (def.SqlLength != 0)
                    colWidth = (def.SqlLength == -1) ? "(max)" : "(" + def.SqlLength + ")";
                sbSQL.AppendFormat("\t{0} {1}{2},\r\n", def.Name, def.SqlDataType, colWidth);
            }

            // Remove last comma and line break
            sbSQL.Remove(sbSQL.Length - 2, 2);

            sbSQL.Append("\n);");
            return sbSQL.ToString();
        }

    }
}
